package com.api.gbhg.luoao.utils;
import com.api.gbhg.liwenjun.VersionFlowUtil;
import jxl.read.biff.Record;
import weaver.conn.RecordSet;


import java.util.List;
import java.util.Map;

public class VersionQuery {

    //查询出 项目名称 申报单位 申报科室 经办人id 流程数据id
    public static RecordSet getversion(String lcbj, String fromname,String requestid){
        RecordSet recordSet = new RecordSet();
        StringBuffer sb = new StringBuffer();
        String sql = "select ver.xmmin,hrm.subcompanyname,tment.departmentmark,ver.jbr,syd.id,ver.lcjlsqjlid,ver.yszje from\n" +
                "uf_jingfei_version ver,\n" +
                fromname+" syd,\n" +
                "hrmsubcompany  hrm,\n" +
                "HrmDepartment tment\n" +
                "where ver.requestId = syd.requestId and \n" +
                "ver.sbdw = hrm.id and ver.sbks = tment.id ";
        sb.append(sql);
        sb.append(" and ver.lcbj = "+lcbj);
        sb.append(" and ver.lcjlsqjlid in ("+requestid+")");

        recordSet.executeQuery(sb.toString());

        return recordSet;
    }

    //查询明细表
    public static RecordSet getdetail(String fromname,int id){
        RecordSet recordSet = new RecordSet();
        recordSet.executeQuery("select * from "+fromname+" where mainid = ?",id);
        return  recordSet;
    }

    //总页数
    public static int getcoutn(String lcbj,String id){
        RecordSet recordSet = new RecordSet();
        StringBuffer sb = new StringBuffer();
        sb.append("select id from uf_jingfei_version where ");
        sb.append("  lcbj = "+lcbj);
        //是否是 已审批 或 未审批的
        if (id.equals("3")){
            sb.append(" and lcztz = 3");
        }else {
            sb.append(" and lcztz <> 3");
        }
        recordSet.executeQuery(sb.toString());
       int count = 0;
        while (recordSet.next()){
            count++;
        }

        return count;
    }

    //查询公共图书馆、阅览室订阅审批列表
    public static RecordSet getbooks(String lcbj,String requestid){
        RecordSet recordSet = new RecordSet();
        StringBuffer sb = new StringBuffer();
        String sql  = "select ver.xmmin,ver.jbr,syd.id,ver.lcjlsqjlid,ver.yszje,\n" +
                "ver.sbks,ver.sbdw,tment.departmentmark,syd.zfcgyswy,syd.hwgz,syd.gc,syd.fw,syd.xecg,hrm.subcompanyname\n" +
                "from\n" +
                "uf_jingfei_version ver,\n" +
                "uf_ggtsgylsdyspbbb syd,\n" +
                "hrmsubcompany  hrm,\n" +
                "HrmDepartment tment\n" +
                "where ver.requestId = syd.requestId and \n" +
                "ver.sbdw = hrm.id and ver.sbks = tment.id ";

        sb.append(sql);
        sb.append(" and ver.lcbj = "+lcbj);
        sb.append(" and ver.lcjlsqjlid in ("+requestid+")");
        recordSet.executeQuery(sb.toString());
        return recordSet;
    }


    //修改流程状态值,流程状态
    public static boolean UpdataStatus(List<String> arraylczt){
        RecordSet recordSet = new RecordSet();
        boolean b = false;
        for(String id : arraylczt){
             String[] flowNodeName = VersionFlowUtil.getFlowNodeName(id);
             b = recordSet.executeUpdate("UPDATE uf_jingfei_version SET lcztz = 3, lczt = ? WHERE lcjlsqjlid = ?",flowNodeName[1],id);
             if (b == false){
                 break;
             }
        }
        return b;
    }



    //查询经费额度表
    public static RecordSet getExpenditure(Map<String,String> map){
        Integer pageNum = Integer.parseInt(map.get("pageNum"));
        Integer pageSize = Integer.parseInt(map.get("pageSize"));
        RecordSet rs = new RecordSet();
        rs.executeQuery("select xmmc,sbdw,SUM(ze) ze,SUM(syed) syed,requestId from " +
                "uf_jingfei where sbdw = ? and nd =? group by requestId,xmmc,sbdw \n" +
                "order by requestId offset ? rows fetch next ? rows only",
                map.get("sbdw"),map.get("nd"),(pageNum - 1) * pageSize,pageSize);
        return rs;
    }


}
